-- 
-- campos agregados para conciliación bancaria en Java;
-- ayudan al manejo de filtros en la conciliacion
-- 

CREATE SCHEMA tiendanube
    AUTHORIZATION abacouser;

CREATE OR REPLACE FUNCTION tiendanube.get_cadenavacia(
	pdummy character varying,
	pdummy2 character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
BEGIN
  RETURN QUERY EXECUTE ('SELECT CAST(''' || ''' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tiendanube.get_cadenavacia(character varying, character varying)
    OWNER TO abacouser;

-- FUNCTION: tiendanube.get_jsonactualizacionstock(integer, character varying, character varying)

-- DROP FUNCTION tiendanube.get_jsonactualizacionstock(integer, character varying, character varying);

CREATE OR REPLACE FUNCTION tiendanube.get_jsonactualizacionstock(
	pidcomm integer,
	pcodambsuc character varying,
	strxml character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
  id_tienda   varchar;
  cCursor    refCursor;
  vCadena       varchar;
  vCadenaNuevo  varchar;
BEGIN
  id_tienda := (SELECT SUBSTRING(notas, position('<TiendaNube>' in notas) + 12,1) FROM ambsuc WHERE Cod = pCodAmbSuc);

  open cCursor for
  	SELECT '{"sku":' || trim(art.cod) || ',"stock":' || cast(artm.cant * artm.signo as varchar ) || '}'
    FROM ARTM
    INNER JOIN ART ON ART.ID = ARTM.IDART
    WHERE ARTM.IDCOMM = pIdComm AND (ART.TIPOARTA = 'A' AND ART.CODARTR ILIKE 'ID.%') AND ARTM.MODIFICASTOCK = 'S';
  
  vCadena := '[';
 
  fetch cCursor into vCadenaNuevo;
  while found loop
     vCadena := vCadena || vCadenaNuevo || ',' ;  
     fetch cCursor into vCadenaNuevo;
  end loop;
  close cCursor;
  if length(vCadena) > 1 then
     vCadena := trim(vCadena,',');
	 vCadena := vCadena || ']';
  end if;
	 
 
  RETURN QUERY EXECUTE ('SELECT CAST(''' || id_tienda || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tiendanube.get_jsonactualizacionstock(integer, character varying, character varying)
    OWNER TO abacouser;

-- FUNCTION: tiendanube.get_ultimaordenprocesada(character varying, character varying)

-- DROP FUNCTION tiendanube.get_ultimaordenprocesada(character varying, character varying);

CREATE OR REPLACE FUNCTION tiendanube.get_ultimaordenprocesada(
	pcodambsuc character varying,
	strxml character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
  UltimaOrden varchar;
  id_tienda   varchar;
BEGIN
  id_tienda := (SELECT SUBSTRING(notas, position('<TiendaNube>' in notas) + 12,1) FROM ambsuc WHERE Cod = pCodAmbSuc);
  -- EL 502907445 ES UN NUMERO MINIMO CONOCIDO
  UltimaOrden := COALESCE((SELECT MAX(txtlibre2) FROM COMM WHERE TIPOCOM = 'PTN'),'1000');
  UltimaOrden := CAST(CAST(UltimaOrden as integer) + 1 AS VARCHAR);

  RETURN QUERY EXECUTE ('SELECT CAST(''' || id_tienda || '/orders?q=' || UltimaOrden || ''' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tiendanube.get_ultimaordenprocesada(character varying, character varying)
    OWNER TO abacouser;

-- FUNCTION: tiendanube.get_ultimaordenprocesada(character varying, character varying)

-- DROP FUNCTION tiendanube.get_ultimaordenprocesada(character varying, character varying);

CREATE OR REPLACE FUNCTION tiendanube.get_ultimaordenprocesada(
	pcodambsuc character varying,
	strxml character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 100
AS $BODY$
DECLARE
  UltimaOrden varchar;
  id_tienda   varchar;
BEGIN
  id_tienda := (SELECT SUBSTRING(notas, position('<TiendaNube>' in notas) + 12,1) FROM ambsuc WHERE Cod = pCodAmbSuc);

  UltimaOrden := COALESCE((SELECT MAX(txtlibre2) FROM COMM WHERE TIPOCOM = 'PTN'),'1000');
  UltimaOrden := CAST(CAST(UltimaOrden as integer) + 1 AS VARCHAR);

  RETURN QUERY EXECUTE ('SELECT CAST(''' || id_tienda || '/orders?q=' || UltimaOrden || ''' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tiendanube.get_ultimaordenprocesada(character varying, character varying)
    OWNER TO abacouser;
-- FUNCTION: tiendanube.procesar_ordenes(character varying, character varying)

-- DROP FUNCTION tiendanube.procesar_ordenes(character varying, character varying);

CREATE OR REPLACE FUNCTION tiendanube.procesar_ordenes(
	pcodambsuc character varying,
	parametros character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE
  paquete varchar;
  i_registro integer;
  i_producto integer;
  
  body json;
  orden json;
  producto json;
  productos json;
  
  vemail varchar;
  vIdPad integer;
  vIdOrden varchar;
  vNroOrden varchar;
  vFecha varchar;
  vstatus varchar;
  vestadodelaorden varchar;
  vestadodelpago varchar;
  vestadodelenvio varchar;
  vnombredelcomprador varchar;
  vdnicuit varchar;
  vtelefono varchar;
  vnombreparaelenvio varchar;
  vtelefonoparaelenvio varchar;
  vdireccionparaelenvio varchar;
  vdomicilioparaelenvio varchar;
  vnumeroparaelenvio varchar;
  vciudadparaelenvio varchar;
  vlocalidadparaelenvio varchar;
  vpisoparaelenvio varchar;
  vprovinciaparaelenvio varchar;
  vcodigopostalparaelenvio varchar;
  vmediodeenvioparaelenvio varchar;
  vmediodepagoparaelenvio varchar;
  vnota  varchar;
  vshipping_option  varchar;
  VGatewayDePago    varchar;

  vLeyenda  varchar;

  vIdArt     varchar;
  vCantidad  varchar;
  vPrecio    varchar; 
  vSKU       varchar;
  vCodStkDep varchar;
 
  vIdArtDesconocido integer;
  vIdPadCF          integer;
  pTipoComF         varchar;
 
  vXML  varchar;

BEGIN
  pTipoComf := 'PTN';
  vIdPadCF := 1031;
  vIdArtDesconocido := 10909;
  vCodStkDep := COALESCE((SELECT CODSTKDEP FROM AMBSUC WHERE AMBSUC.COD = pCodAmbSuc),'D0');
  
  paquete := replace(parametros,'##@','{');
  paquete := replace(paquete,'@##','}');
  paquete := replace(paquete,'###',':');

  body := json_extract_path(cast(paquete as json),'body');
  i_registro := 0;
  
  vXML :=  '<Movimientos>';

  WHILE i_registro <=  json_array_length(body) - 1 LOOP
     orden  := body -> i_registro;
	 vIdOrden := orden ->> 'id';
	 vNroOrden := orden ->> 'number';
	 vemail := orden -> 'customer' ->> 'email';
	 vIdPad := coalesce((SELECT id FROM PAD where (accesosucursales ilike '%' || pCodambsuc || '%' or accesosucursales = '*') and email = vemail limit 1),vIdPadCf);
	 vFecha := substring(orden ->> 'created_at',1,10); -- 2021-12-05
	 vestadodelaorden  := orden ->> 'status';
	 vestadodelpago  := orden ->> 'payment_status'; 
	 vestadodelenvio := orden ->> 'shipping_status';
	 vnombredelcomprador := orden -> 'customer' ->>'name';
	 vdnicuit  := orden -> 'customer' ->> 'identification';
	 vtelefono := orden -> 'customer' ->> 'phone';
	 vnombreparaelenvio    := orden -> 'shipping_address' ->>'name';
	 vtelefonoparaelenvio  := orden -> 'shipping_address' ->>'phone';
	 vdomicilioparaelenvio := orden -> 'shipping_address' ->>'address';
	 vnumeroparaelenvio    := orden -> 'shipping_address' ->>'number';
	 vciudadparaelenvio    := orden -> 'shipping_address' ->>'city';
	 vlocalidadparaelenvio := orden -> 'shipping_address' ->>'locality';
	 vpisoparaelenvio      := orden -> 'shipping_address' ->>'floor';
	 vprovinciaparaelenvio := orden -> 'shipping_address' ->>'province';
	 vcodigopostalparaelenvio := orden -> 'shipping_address' ->>'zipcode';
	 vmediodeenvioparaelenvio := orden ->> 'shipping';
	 vmediodepagoparaelenvio  := orden ->  'payment_details' ->>'method';
	 vnota         := orden ->> 'note';
	 vshipping_option         := orden ->> 'shipping_option';
	 vGatewayDePago           := orden ->> 'gateway';
 
     vLeyenda :=  'Envio: ' || vshipping_option || chr(10) ||
	          'email: ' || str_sin_acentos(vemail) || chr(10) ||
			  'Orden: ' || str_sin_acentos(vestadodelaorden) || chr(10) ||
			  'Pago : ' || str_sin_acentos(vestadodelpago)   || chr(10) ||
			  'Envio: ' || str_sin_acentos(vestadodelenvio)  || chr(10) ||
			  CASE WHEN vIdPad IS NULL THEN
			      'Nombre: ' ||   str_sin_acentos(vnombredelcomprador) || chr(10) ||
			      'DNI/CUIT: ' || str_sin_acentos(vdnicuit) || chr(10) ||
			      'Telefono: ' || vtelefono || chr(10) 
		      ELSE '' END ||
			  'Pagado por: ' || str_sin_acentos(vGatewayDePago) || chr(10) ||
			  'Nombre envio: ' || str_sin_acentos(vnombreparaelenvio) || chr(10) ||
			  'Telefono envio: ' || vtelefonoparaelenvio              || chr(10) ||
       		  'Direccion: ' || str_sin_acentos(vdomicilioparaelenvio) || ' Nro: ' || vnumeroparaelenvio || 
			  'Piso: ' || vpisoparaelenvio || chr(10) ||
			  'Localidad: ' || str_sin_acentos(vlocalidadparaelenvio) || ' Ciudad: ' || str_sin_acentos(vciudadparaelenvio) || chr(10) || 
			  'C.Postal: '  || vcodigopostalparaelenvio || ' Provincia: ' || vprovinciaparaelenvio || chr(10) || 
              'Envio: ' || str_sin_acentos(vmediodeenvioparaelenvio) || 
			  'Medio de pago: ' || str_sin_acentos(vmediodepagoparaelenvio) || chr(10) || 
			  'Nota: ' || str_sin_acentos(vnota);

	
			vXML := vXML || '<Comprobante TipoCom = "' ||  pTipoComF  
                                         || '" Referencia = "' || CAST(vidpad AS VARCHAR) ||'" >' || 
						 '<Encabezado>' || 
                         '<IdPad>'   || CAST(vidpad AS VARCHAR) || '</IdPad><Fecha> ' || to_char(cast(vfecha as date), 'DD/MM/YY') || '</Fecha>' ||
                         '<Leyenda>' || vLeyenda || '</Leyenda>' ||
						 '<txtlibre1>' || vIdOrden || '</txtlibre1>' ||
						 '<txtlibre2>' || vNroOrden || '</txtlibre2>' ||
						 '<detalle> Orden: ' || vNroOrden || '</detalle>' ||
						 '<GeneradoPor>ImpTN</GeneradoPor><CodAmbSuc>' || pCodAmbSuc || '</CodAmbSuc>';
			vXML := vXML || '</Encabezado><Renglones>';
			
			i_registro := i_registro + 1;

            productos := orden -> 'products';
			i_producto := 0;
			WHILE i_producto <= json_array_length(productos) - 1 LOOP
				producto := productos -> i_producto;
				vIdArt    := cast(coalesce((SELECT ID FROM ART WHERE COD = producto ->> 'sku'),vIdArtDesconocido) as varchar);
				vCantidad := producto ->> 'quantity';
				vPrecio   := producto ->> 'price';
				vSKU      := producto ->> 'sku';
				vXML := vXML || 
				   CASE WHEN false  THEN '<RenglonArticulo Signo = "Entrada">' 
                                    ELSE '<RenglonArticulo Signo = "Salida">'
                   END || 
                   '<IdArt> '  || vIDART    || '</IdArt>' ||
                   '<Cant>'    || vCANTIDAD || '</Cant>';
                 vXML := vXML || '<PrecioCIva>'  || vprecio || '</PrecioCIva>';
                 vXML := vXML || '<PrecioLista>' || vprecio || '</PrecioLista>';
                 vXML := vXML || '<Descripgrande>' || coalesce(vSKU,'') || '</Descripgrande>';
                 vXML := vXML || '<CodStkDep>' || vCodStkDep || '</CodStkDep>';
                 vXML := vXML || '</RenglonArticulo>';
				 i_producto := i_producto + 1;
			END LOOP;
            vXML := vXML || '</Renglones>';
			vXML := vXML || '</Comprobante>';
  	  END LOOP;
	  vXML := vXML || '</Movimientos>';
	  vXML := str_sin_acentos(vXML);

RETURN QUERY EXECUTE ('SELECT cast(' || '''' || vXML || '''' || ' as varchar) as XML');


END;
$BODY$;

ALTER FUNCTION tiendanube.procesar_ordenes(character varying, character varying)
    OWNER TO abacouser;

-- =================================
SELECT aaVersion('469.00','468.03');
-- =================================

